<!DOCTYPE html>












  


<html class="theme-next pisces use-motion" lang="zh-Hans">
<head><meta name="generator" content="Hexo 3.8.0">
  <!-- hexo-inject:begin --><!-- hexo-inject:end --><meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=2">
<meta name="theme-color" content="#222">


  
  
  <link rel="stylesheet" href="/xieyuanhui/lib/needsharebutton/needsharebutton.css">

















  
  
  <link rel="stylesheet" href="/xieyuanhui/lib/fancybox/source/jquery.fancybox.css">







<link rel="stylesheet" href="/xieyuanhui/lib/font-awesome/css/font-awesome.min.css?v=4.6.2">

<link rel="stylesheet" href="/xieyuanhui/css/main.css?v=7.1.0">


  <link rel="apple-touch-icon" sizes="180x180" href="/xieyuanhui/images/apple-touch-icon-next.png?v=7.1.0">


  <link rel="icon" type="image/png" sizes="32x32" href="/xieyuanhui/images/favicon-32x32-next.png?v=7.1.0">


  <link rel="icon" type="image/png" sizes="16x16" href="/xieyuanhui/images/favicon-16x16-next.png?v=7.1.0">


  <link rel="mask-icon" href="/xieyuanhui/images/logo.svg?v=7.1.0" color="#222">


  <link rel="manifest" href="/xieyuanhui/images/manifest.json">


  <meta name="msapplication-config" content="/xieyuanhui/images/browserconfig.xml">





<script id="hexo.configurations">
  var NexT = window.NexT || {};
  var CONFIG = {
    root: '/xieyuanhui/',
    scheme: 'Pisces',
    version: '7.1.0',
    sidebar: {"position":"left","display":"post","offset":12,"onmobile":false,"dimmer":false},
    back2top: true,
    back2top_sidebar: false,
    fancybox: true,
    fastclick: false,
    lazyload: false,
    tabs: true,
    motion: {"enable":true,"async":false,"transition":{"post_block":"fadeIn","post_header":"slideDownIn","post_body":"slideDownIn","coll_header":"slideLeftIn","sidebar":"slideUpIn"}},
    algolia: {
      applicationID: '',
      apiKey: '',
      indexName: '',
      hits: {"per_page":10},
      labels: {"input_placeholder":"Search for Posts","hits_empty":"We didn't find any results for the search: ${query}","hits_stats":"${hits} results found in ${time} ms"}
    }
  };
</script>


  




  <meta name="description" content="原文地址：史上最全SQL优化方案 在进行MySQL的优化之前，必须要了解的就是MySQL的查询过程，很多查询优化工作实际上就是遵循一些原则，让MySQL的优化器能够按照预想的合理方式运行而已。  优化的哲学注：优化有风险，涉足需谨慎 优化可能带来的问题？ 优化不总是对一个单纯的环境进行，还很可能是一个复杂的已投产的系统； 优化手段本来就有很大的风险，只不过你没能力意识到和预见到； 任何的技术可以解">
<meta name="keywords" content="数据库,SQL">
<meta property="og:type" content="article">
<meta property="og:title" content="SQL优化方案">
<meta property="og:url" content="http://xyh5513.gitee.io/xieyuanhui/2019/07/28/SQL优化方案/index.html">
<meta property="og:site_name" content="xieyuanhui的笔记">
<meta property="og:description" content="原文地址：史上最全SQL优化方案 在进行MySQL的优化之前，必须要了解的就是MySQL的查询过程，很多查询优化工作实际上就是遵循一些原则，让MySQL的优化器能够按照预想的合理方式运行而已。  优化的哲学注：优化有风险，涉足需谨慎 优化可能带来的问题？ 优化不总是对一个单纯的环境进行，还很可能是一个复杂的已投产的系统； 优化手段本来就有很大的风险，只不过你没能力意识到和预见到； 任何的技术可以解">
<meta property="og:locale" content="zh-Hans">
<meta property="og:image" content="http://xyh5513.gitee.io/xieyuanhui/2019/07/28/SQL优化方案/img1.jpeg">
<meta property="og:image" content="http://xyh5513.gitee.io/xieyuanhui/2019/07/28/SQL优化方案/img2.jpeg">
<meta property="og:image" content="http://xyh5513.gitee.io/xieyuanhui/2019/07/28/SQL优化方案/img3.jpeg">
<meta property="og:image" content="http://xyh5513.gitee.io/xieyuanhui/2019/07/28/SQL优化方案/img4.jpeg">
<meta property="og:image" content="http://xyh5513.gitee.io/xieyuanhui/2019/07/28/SQL优化方案/img5.jpeg">
<meta property="og:image" content="http://xyh5513.gitee.io/xieyuanhui/2019/07/28/SQL优化方案/img6.jpeg">
<meta property="og:image" content="http://xyh5513.gitee.io/xieyuanhui/2019/07/28/SQL优化方案/img7.jpeg">
<meta property="og:image" content="http://xyh5513.gitee.io/xieyuanhui/2019/07/28/SQL优化方案/img8.jpeg">
<meta property="og:image" content="http://xyh5513.gitee.io/xieyuanhui/2019/07/28/SQL优化方案/img9.jpeg">
<meta property="og:updated_time" content="2019-09-01T02:14:38.700Z">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="SQL优化方案">
<meta name="twitter:description" content="原文地址：史上最全SQL优化方案 在进行MySQL的优化之前，必须要了解的就是MySQL的查询过程，很多查询优化工作实际上就是遵循一些原则，让MySQL的优化器能够按照预想的合理方式运行而已。  优化的哲学注：优化有风险，涉足需谨慎 优化可能带来的问题？ 优化不总是对一个单纯的环境进行，还很可能是一个复杂的已投产的系统； 优化手段本来就有很大的风险，只不过你没能力意识到和预见到； 任何的技术可以解">
<meta name="twitter:image" content="http://xyh5513.gitee.io/xieyuanhui/2019/07/28/SQL优化方案/img1.jpeg">



  <link rel="alternate" href="/xieyuanhui/atom.xml" title="xieyuanhui的笔记" type="application/atom+xml">



  
  
  <link rel="canonical" href="http://xyh5513.gitee.io/xieyuanhui/2019/07/28/SQL优化方案/">



<script id="page.configurations">
  CONFIG.page = {
    sidebar: "",
  };
</script>

  <title>SQL优化方案 | xieyuanhui的笔记</title>
  












  <noscript>
  <style>
  .use-motion .motion-element,
  .use-motion .brand,
  .use-motion .menu-item,
  .sidebar-inner,
  .use-motion .post-block,
  .use-motion .pagination,
  .use-motion .comments,
  .use-motion .post-header,
  .use-motion .post-body,
  .use-motion .collection-title { opacity: initial; }

  .use-motion .logo,
  .use-motion .site-title,
  .use-motion .site-subtitle {
    opacity: initial;
    top: initial;
  }

  .use-motion .logo-line-before i { left: initial; }
  .use-motion .logo-line-after i { right: initial; }
  </style>
</noscript><!-- hexo-inject:begin --><!-- hexo-inject:end -->

</head>

<body itemscope itemtype="http://schema.org/WebPage" lang="zh-Hans">

  
  
    
  

  <!-- hexo-inject:begin --><!-- hexo-inject:end --><div class="container sidebar-position-left page-post-detail">
    <div class="headband"></div>

    <header id="header" class="header" itemscope itemtype="http://schema.org/WPHeader">
      <div class="header-inner"><div class="site-brand-wrapper">
  <div class="site-meta">
    

    <div class="custom-logo-site-title">
      <a href="/xieyuanhui/" class="brand" rel="start">
        <span class="logo-line-before"><i></i></span>
        <span class="site-title">xieyuanhui的笔记</span>
        <span class="logo-line-after"><i></i></span>
      </a>
    </div>
    
    
  </div>

  <div class="site-nav-toggle">
    <button aria-label="切换导航栏">
      <span class="btn-bar"></span>
      <span class="btn-bar"></span>
      <span class="btn-bar"></span>
    </button>
  </div>
</div>



<nav class="site-nav">
  
    <ul id="menu" class="menu">
      
        
        
        
          
          <li class="menu-item menu-item-home">

    
    
    
      
    

    

    <a href="/xieyuanhui/" rel="section"><i class="menu-item-icon fa fa-fw fa-home"></i> <br>首页</a>

  </li>
        
        
        
          
          <li class="menu-item menu-item-about">

    
    
    
      
    

    

    <a href="/xieyuanhui/about/" rel="section"><i class="menu-item-icon fa fa-fw fa-user"></i> <br>关于</a>

  </li>
        
        
        
          
          <li class="menu-item menu-item-tags">

    
    
    
      
    

    

    <a href="/xieyuanhui/tags/" rel="section"><i class="menu-item-icon fa fa-fw fa-tags"></i> <br>标签</a>

  </li>
        
        
        
          
          <li class="menu-item menu-item-categories">

    
    
    
      
    

    

    <a href="/xieyuanhui/categories/" rel="section"><i class="menu-item-icon fa fa-fw fa-th"></i> <br>分类</a>

  </li>
        
        
        
          
          <li class="menu-item menu-item-archives">

    
    
    
      
    

    

    <a href="/xieyuanhui/archives/" rel="section"><i class="menu-item-icon fa fa-fw fa-archive"></i> <br>归档</a>

  </li>

      
      
        <li class="menu-item menu-item-search">
          
            <a href="javascript:;" class="popup-trigger">
          
            
              <i class="menu-item-icon fa fa-search fa-fw"></i> <br>搜索</a>
        </li>
      
    </ul>
  

  

  
    <div class="site-search">
      
  <div class="popup search-popup local-search-popup">
  <div class="local-search-header clearfix">
    <span class="search-icon">
      <i class="fa fa-search"></i>
    </span>
    <span class="popup-btn-close">
      <i class="fa fa-times-circle"></i>
    </span>
    <div class="local-search-input-wrapper">
      <input autocomplete="off" placeholder="搜索..." spellcheck="false" type="text" id="local-search-input">
    </div>
  </div>
  <div id="local-search-result"></div>
</div>



    </div>
  
</nav>



  



</div>
    </header>

    


    <main id="main" class="main">
      <div class="main-inner">
        <div class="content-wrap">
          
            

          
          <div id="content" class="content">
            

  <div id="posts" class="posts-expand">
    

  

  
  
  

  

  <article class="post post-type-normal" itemscope itemtype="http://schema.org/Article">
  
  
  
  <div class="post-block">
    <link itemprop="mainEntityOfPage" href="http://xyh5513.gitee.io/xieyuanhui/xieyuanhui/2019/07/28/SQL优化方案/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="name" content="xieyuanhui">
      <meta itemprop="description" content>
      <meta itemprop="image" content="/xieyuanhui/images/deer.png">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="xieyuanhui的笔记">
    </span>

    
      <header class="post-header">

        
        
          <h1 class="post-title" itemprop="name headline">SQL优化方案

              
            
          </h1>
        

        <div class="post-meta">
          <span class="post-time">

            
            
            

            
              <span class="post-meta-item-icon">
                <i class="fa fa-calendar-o"></i>
              </span>
              
                <span class="post-meta-item-text">发表于</span>
              

              
                
              

              <time title="创建时间：2019-07-28 14:29:21" itemprop="dateCreated datePublished" datetime="2019-07-28T14:29:21+08:00">2019-07-28</time>
            

            
              

              
                
                <span class="post-meta-divider">|</span>
                

                <span class="post-meta-item-icon">
                  <i class="fa fa-calendar-check-o"></i>
                </span>
                
                  <span class="post-meta-item-text">更新于</span>
                
                <time title="修改时间：2019-09-01 10:14:38" itemprop="dateModified" datetime="2019-09-01T10:14:38+08:00">2019-09-01</time>
              
            
          </span>

          
            <span class="post-category">
            
              <span class="post-meta-divider">|</span>
            
              <span class="post-meta-item-icon">
                <i class="fa fa-folder-o"></i>
              </span>
              
                <span class="post-meta-item-text">分类于</span>
              
              
                <span itemprop="about" itemscope itemtype="http://schema.org/Thing"><a href="/xieyuanhui/categories/数据库/" itemprop="url" rel="index"><span itemprop="name">数据库</span></a></span>

                
                
              
            </span>
          

          
            
            
          

          
          
            <span id="/xieyuanhui/2019/07/28/SQL优化方案/" class="leancloud_visitors" data-flag-title="SQL优化方案">
              <span class="post-meta-divider">|</span>
              <span class="post-meta-item-icon">
                <i class="fa fa-eye"></i>
              </span>
              
                <span class="post-meta-item-text">阅读次数：</span>
              
                <span class="leancloud-visitors-count"></span>
            </span>
          

          

          
            <div class="post-symbolscount">
              

              
                <span class="post-meta-item-icon">
                  <i class="fa fa-file-word-o"></i>
                </span>
                
                  <span class="post-meta-item-text">本文字数：</span>
                
                <span title="本文字数">11k</span>
              

              
                <span class="post-meta-divider">|</span>
              

              
                <span class="post-meta-item-icon">
                  <i class="fa fa-clock-o"></i>
                </span>
                
                  <span class="post-meta-item-text">阅读时长 &asymp;</span>
                
                <span title="阅读时长">10 分钟</span>
              
            </div>
          

          

        </div>
      </header>
    

    
    
    
    <div class="post-body" itemprop="articleBody">

      
      

      
        <p>原文地址：<a href="http://blog.itpub.net/31555484/viewspace-2565387/" target="_blank" rel="noopener">史上最全SQL优化方案</a></p>
<p>在进行MySQL的优化之前，必须要了解的就是MySQL的查询过程，很多查询优化工作实际上就是遵循一些原则，让MySQL的优化器能够按照预想的合理方式运行而已。</p>
<p><img src="/xieyuanhui/2019/07/28/SQL优化方案/img1.jpeg" alt="MySQL查询过程"></p>
<h2 id="优化的哲学"><a href="#优化的哲学" class="headerlink" title="优化的哲学"></a>优化的哲学</h2><p>注：优化有风险，涉足需谨慎</p>
<h3 id="优化可能带来的问题？"><a href="#优化可能带来的问题？" class="headerlink" title="优化可能带来的问题？"></a>优化可能带来的问题？</h3><ul>
<li>优化不总是对一个单纯的环境进行，还很可能是一个复杂的已投产的系统；</li>
<li>优化手段本来就有很大的风险，只不过你没能力意识到和预见到；</li>
<li>任何的技术可以解决一个问题，但必然存在带来一个问题的风险；</li>
<li>对于优化来说解决问题而带来的问题，控制在可接受的范围内才是有成果；</li>
<li>保持现状或出现更差的情况都是失败！</li>
</ul>
<h3 id="优化的需求？"><a href="#优化的需求？" class="headerlink" title="优化的需求？"></a>优化的需求？</h3><ul>
<li>稳定性和业务可持续性，通常比性能更重要；</li>
<li>优化不可避免涉及到变更，变更就有风险；</li>
<li>优化使性能变好，维持和变差是等概率事件；</li>
<li>切记优化，应该是各部门协同，共同参与的工作，任何单一部门都不能对数据库进行优化！</li>
</ul>
<p>所以优化工作，是由业务需要驱使的！</p>
<h3 id="优化由谁参与？"><a href="#优化由谁参与？" class="headerlink" title="优化由谁参与？"></a>优化由谁参与？</h3><p>在进行数据库优化时，应由数据库管理员、业务部门代表、应用程序架构师、应用程序设计人员、应用程序开发人员、硬件及系统管理员、存储管理员等，业务相关人员共同参与。 </p>
<h2 id="优化思路"><a href="#优化思路" class="headerlink" title="优化思路"></a>优化思路</h2><h3 id="优化什么？"><a href="#优化什么？" class="headerlink" title="优化什么？"></a>优化什么？</h3><p>在数据库优化上有两个主要方面：即安全与性能。</p>
<ul>
<li>安全-&gt;数据可持续性；</li>
<li>性能-&gt;数据的高性能访问。</li>
</ul>
<h3 id="优化的范围有哪些？"><a href="#优化的范围有哪些？" class="headerlink" title="优化的范围有哪些？"></a>优化的范围有哪些？</h3><h4 id="存储、主机和操作系统方面："><a href="#存储、主机和操作系统方面：" class="headerlink" title="存储、主机和操作系统方面："></a>存储、主机和操作系统方面：</h4><ul>
<li>主机架构稳定性；</li>
<li>I/O规划及配置；</li>
<li>Swap交换分区；</li>
<li>OS内核参数和网络问题。</li>
</ul>
<h4 id="应用程序方面："><a href="#应用程序方面：" class="headerlink" title="应用程序方面："></a>应用程序方面：</h4><ul>
<li>应用程序稳定性；</li>
<li>SQL语句性能；</li>
<li>串行访问资源；</li>
<li>性能欠佳会话管理；</li>
<li>这个应用适不适合用MySQL。</li>
</ul>
<h4 id="数据库优化方面："><a href="#数据库优化方面：" class="headerlink" title="数据库优化方面："></a>数据库优化方面：</h4><ul>
<li>内存；</li>
<li>数据库结构（物理&amp;逻辑）；</li>
<li>实例配置。</li>
</ul>
<p>说明：不管是设计系统、定位问题还是优化，都可以按照这个顺序执行。</p>
<h3 id="优化维度？"><a href="#优化维度？" class="headerlink" title="优化维度？"></a>优化维度？</h3><h4 id="数据库的优化维度有四个："><a href="#数据库的优化维度有四个：" class="headerlink" title="数据库的优化维度有四个："></a>数据库的优化维度有四个：</h4><p>硬件、系统配置、数据库表结构、SQL及索引。</p>
<p><img src="/xieyuanhui/2019/07/28/SQL优化方案/img2.jpeg" alt></p>
<h4 id="优化选择："><a href="#优化选择：" class="headerlink" title="优化选择："></a>优化选择：</h4><ul>
<li>优化成本：硬件&gt;系统配置&gt;数据库表结构&gt;SQL及索引。</li>
<li>优化效果：硬件&lt;系统配置&lt;数据库表结构&lt;SQL及索引。</li>
</ul>
<h2 id="优化工具有啥？"><a href="#优化工具有啥？" class="headerlink" title="优化工具有啥？"></a>优化工具有啥？</h2><h3 id="数据库层面"><a href="#数据库层面" class="headerlink" title="数据库层面"></a>数据库层面</h3><p>检查问题常用工具：</p>
<p>1）MySQL</p>
<p>2）msyqladmin：MySQL客户端，可进行管理操作</p>
<p>3）mysqlshow：功能强大的查看shell命令</p>
<p>4）show [SESSION | GLOBAL] variables：查看数据库参数信息</p>
<p>5）SHOW [SESSION | GLOBAL] STATUS：查看数据库的状态信息</p>
<p>6）information_schema：获取元数据的方法</p>
<p>7）SHOW ENGINE INNODB STATUS：Innodb引擎的所有状态</p>
<p>8）SHOW PROCESSLIST：查看当前所有连接session状态</p>
<p>9）explain：获取查询语句的执行计划</p>
<p>10）show index：查看表的索引信息</p>
<p>11）slow-log：记录慢查询语句</p>
<p>12）mysqldumpslow：分析slowlog文件的</p>
<p>不常用但好用的工具：</p>
<p>1）Zabbix：监控主机、系统、数据库（部署zabbix监控平台）</p>
<p>2）pt-query-digest：分析慢日志</p>
<p>3）MySQL slap：分析慢日志</p>
<p>4）sysbench：压力测试工具</p>
<p>5）MySQL profiling：统计数据库整体状态工具    </p>
<p>6）Performance Schema：MySQL性能状态统计的数据</p>
<p>7）workbench：管理、备份、监控、分析、优化工具（比较费资源）</p>
<h3 id="数据库层面问题解决思路？"><a href="#数据库层面问题解决思路？" class="headerlink" title="数据库层面问题解决思路？"></a>数据库层面问题解决思路？</h3><p>一般应急调优的思路：针对突然的业务办理卡顿，无法进行正常的业务处理，需要立马解决的场景。</p>
<p>1）show processlist；</p>
<p>2）explain  select id ,name from stu where name=’clsn’; # ALL  id name age  sex；</p>
<p>select id,name from stu  where id=2-1 函数 结果集&gt;30；show index from table；</p>
<p>3）通过执行计划判断，索引问题（有没有、合不合理）或者语句本身问题；</p>
<p>4）show status  like ‘%lock%’;    # 查询锁状态</p>
<p>kill SESSION_ID;   # 杀掉有问题的session。</p>
<p>常规调优思路：针对业务周期性的卡顿，例如在每天10-11点业务特别慢，但是还能够使用，过了这段时间就好了。</p>
<p>1）查看slowlog，分析slowlog，分析出查询慢的语句；</p>
<p>2）按照一定优先级，一个一个排查所有慢语句；</p>
<p>3）分析top SQL，进行explain调试，查看语句执行时间；</p>
<p>4）调整索引或语句本身。</p>
<h3 id="系统层面"><a href="#系统层面" class="headerlink" title="系统层面"></a>系统层面</h3><h4 id="CPU-方面"><a href="#CPU-方面" class="headerlink" title="CPU 方面"></a>CPU 方面</h4><p>vmstat、sar top、htop、nmon、mpstat；</p>
<h4 id="内存"><a href="#内存" class="headerlink" title="内存"></a>内存</h4><p>free 、ps-aux；</p>
<h4 id="IO-设备（磁盘、网络）"><a href="#IO-设备（磁盘、网络）" class="headerlink" title="IO 设备（磁盘、网络）"></a>IO 设备（磁盘、网络）</h4><p>iostat、ss、netstat、iptraf、iftop、lsof；</p>
<h4 id="vmstat-命令说明："><a href="#vmstat-命令说明：" class="headerlink" title="vmstat 命令说明："></a>vmstat 命令说明：</h4><p>1）Procs：r显示有多少进程正在等待CPU时间。b显示处于不可中断的休眠的进程数量。在等待I/O。</p>
<p>2）Memory：swpd显示被交换到磁盘的数据块的数量。未被使用的数据块，用户缓冲数据块，用于操作系统的数据块的数量。</p>
<p>3）Swap：操作系统每秒从磁盘上交换到内存和从内存交换到磁盘的数据块的数量。s1和s0最好是0。</p>
<p>4）Io：每秒从设备中读入b1的写入到设备b0的数据块的数量。反映了磁盘I/O。</p>
<p>5）System：显示了每秒发生中断的数量（in）和上下文交换（cs）的数量。</p>
<p>6）Cpu：显示用于运行用户代码，系统代码，空闲，等待I/O的Cpu时间。</p>
<h4 id="iostat-命令说明："><a href="#iostat-命令说明：" class="headerlink" title="iostat 命令说明："></a>iostat 命令说明：</h4><p>实例命令：iostat -dk 1 5</p>
<p>　　　　   iostat -d -k -x 5 （查看设备使用率（%util）和响应时间（await））</p>
<p>1）tps：该设备每秒的传输次数。“一次传输”意思是“一次I/O请求”。多个逻辑请求可能会被合并为“一次I/O请求”。</p>
<p>2）iops ：硬件出厂的时候，厂家定义的一个每秒最大的IO次数</p>
<p>3）”一次传输”请求的大小是未知的。</p>
<p>4）kB_read/s：每秒从设备（drive expressed）读取的数据量；</p>
<p>5）KB_wrtn/s：每秒向设备（drive expressed）写入的数据量；</p>
<p>6）kB_read：读取的总数据量；</p>
<p>7）kB_wrtn：写入的总数量数据量；这些单位都为Kilobytes。</p>
<h3 id="系统层面问题解决办法？"><a href="#系统层面问题解决办法？" class="headerlink" title="系统层面问题解决办法？"></a>系统层面问题解决办法？</h3><p>你认为到底负载高好，还是低好呢？在实际的生产中，一般认为Cpu只要不超过90%都没什么问题。</p>
<p>当然不排除下面这些特殊情况：</p>
<h4 id="CPU-负载高，IO-负载低："><a href="#CPU-负载高，IO-负载低：" class="headerlink" title="CPU 负载高，IO 负载低："></a>CPU 负载高，IO 负载低：</h4><p>1）内存不够；</p>
<p>2）磁盘性能差；</p>
<p>3）SQL问题—-&gt;去数据库层，进一步排查SQL 问题；</p>
<p>4）IO出问题了（磁盘到临界了、raid设计不好、raid降级、锁、在单位时间内tps过高）；</p>
<p>5）tps过高：大量的小数据IO、大量的全表扫描。</p>
<h4 id="IO-负载高，CPU-负载低"><a href="#IO-负载高，CPU-负载低" class="headerlink" title="IO 负载高，CPU 负载低"></a>IO 负载高，CPU 负载低</h4><p>1）大量小的IO写操作：</p>
<p>autocommit，产生大量小IO；IO/PS，磁盘的一个定值，硬件出厂的时候，厂家定义的一个每秒最大的IO次数。</p>
<p>2）大量大的IO 写操作：SQL问题的几率比较大</p>
<h4 id="IO-和-CPU-负载都很高"><a href="#IO-和-CPU-负载都很高" class="headerlink" title="IO 和 CPU 负载都很高"></a>IO 和 CPU 负载都很高</h4><p>硬件不够了或SQL存在问题。</p>
<h2 id="基础优化"><a href="#基础优化" class="headerlink" title="基础优化"></a>基础优化</h2><h3 id="优化思路？"><a href="#优化思路？" class="headerlink" title="优化思路？"></a>优化思路？</h3><p><strong>定位问题点顺序：</strong>硬件—&gt;系统—&gt;应用—&gt;数据库—&gt;架构（高可用、读写分离、分库分表）。</p>
<p><strong>处理方向：</strong>明确优化目标、性能和安全的折中、防患未然。</p>
<h3 id="硬件优化？"><a href="#硬件优化？" class="headerlink" title="硬件优化？"></a>硬件优化？</h3><h4 id="主机方面："><a href="#主机方面：" class="headerlink" title="主机方面："></a>主机方面：</h4><p>根据数据库类型，主机CPU选择、内存容量选择、磁盘选择：</p>
<p>1）平衡内存和磁盘资源；</p>
<p>2）随机的I/O和顺序的I/O；</p>
<p>3）主机 RAID卡的BBU（Battery Backup Unit）关闭。</p>
<h4 id="CPU-的选择"><a href="#CPU-的选择" class="headerlink" title="CPU 的选择"></a>CPU 的选择</h4><p>CPU的两个关键因素：核数、主频</p>
<p>根据不同的业务类型进行选择：</p>
<p>1）CPU密集型：计算比较多，OLTP - 主频很高的cpu、核数还要多</p>
<p>2）IO密集型：查询比较，OLAP - 核数要多，主频不一定高的</p>
<h4 id="内存的选择"><a href="#内存的选择" class="headerlink" title="内存的选择"></a>内存的选择</h4><p>OLAP类型数据库，需要更多内存，和数据获取量级有关。</p>
<p>OLTP类型数据一般内存是Cpu核心数量的2倍到4倍，没有最佳实践。</p>
<h4 id="存储方面"><a href="#存储方面" class="headerlink" title="存储方面"></a>存储方面</h4><p>1）根据存储数据种类的不同，选择不同的存储设备；</p>
<p>2）配置合理的RAID级别（raid5、raid10、热备盘）；</p>
<p>3）对与操作系统来讲，不需要太特殊的选择，最好做好冗余（raid1）（ssd、sas、sata）。</p>
<p>4）raid卡：</p>
<p>​       主机raid卡选择：</p>
<p>​           实现操作系统磁盘的冗余（raid1）；</p>
<p>​           平衡内存和磁盘资源；</p>
<p>​           随机的I/O和顺序的I/O；</p>
<p>​           主机raid卡的BBU（Battery Backup Unit）要关闭。</p>
<h4 id="网络设备方面"><a href="#网络设备方面" class="headerlink" title="网络设备方面"></a>网络设备方面</h4><p>使用流量支持更高的网络设备（交换机、路由器、网线、网卡、HBA卡）</p>
<p>注意：以上这些规划应该在初始设计系统时就应该考虑好。</p>
<h3 id="服务器硬件优化"><a href="#服务器硬件优化" class="headerlink" title="服务器硬件优化"></a>服务器硬件优化</h3><p>1）物理状态灯</p>
<p>2）自带管理设备：远程控制卡（FENCE设备：ipmi ilo idarc）、开关机、硬件监控。</p>
<p>3）第三方的监控软件、设备（snmp、agent）对物理设施进行监控。</p>
<p>4）存储设备：自带的监控平台。EMC2（hp收购了）、 日立（hds）、IBM低端OEM hds、高端存储是自己技术，华为存储。</p>
<h3 id="系统优化"><a href="#系统优化" class="headerlink" title="系统优化"></a>系统优化</h3><h4 id="CPU："><a href="#CPU：" class="headerlink" title="CPU："></a>CPU：</h4><p>基本不需要调整，在硬件选择方面下功夫即可。</p>
<h4 id="内存："><a href="#内存：" class="headerlink" title="内存："></a>内存：</h4><p>基本不需要调整，在硬件选择方面下功夫即可。</p>
<h4 id="SWAP："><a href="#SWAP：" class="headerlink" title="SWAP："></a>SWAP：</h4><p>MySQL尽量避免使用swap。</p>
<p>阿里云的服务器中默认swap为0。</p>
<h4 id="IO"><a href="#IO" class="headerlink" title="IO"></a>IO</h4><p>raid、no lvm、ext4或xfs、ssd、IO调度策略。</p>
<h4 id="SWAP-调整（不使用-swap-分区）"><a href="#SWAP-调整（不使用-swap-分区）" class="headerlink" title="SWAP 调整（不使用 swap 分区）"></a>SWAP 调整（不使用 swap 分区）</h4><p>/proc/sys/vm/swappiness的内容改成0（临时），/etc/sysctl. conf上添加vm.swappiness=0（永久）</p>
<p>这个参数决定了Linux是倾向于使用swap，还是倾向于释放文件系统cache。在内存紧张的情况下，数值越低越倾向于释放文件系统cache。</p>
<p>当然，这个参数只能减少使用swap的概率，并不能避免Linux使用swap。</p>
<h4 id="修改MySQL的配置参数-innodb-flush-method，开启O-DIRECT模式："><a href="#修改MySQL的配置参数-innodb-flush-method，开启O-DIRECT模式：" class="headerlink" title="修改MySQL的配置参数**innodb_flush_ method，开启O_DIRECT模式："></a>修改MySQL的配置参数<em>**</em>innodb_flush_ method，开启O_DIRECT模式：</h4><p>这种情况下，InnoDB的buffer pool会直接绕过文件系统cache来访问磁盘，但是redo log依旧会使用文件系统cache。</p>
<p>值得注意的是，Redo log是覆写模式的，即使使用了文件系统的cache，也不会占用太多。</p>
<h4 id="IO-调度策略："><a href="#IO-调度策略：" class="headerlink" title="IO 调度策略："></a>IO 调度策略：</h4><p>#echo deadline&gt;/sys/block/sda/queue/scheduler   临时修改为deadline</p>
<p>永久修改</p>
<p>vi /boot/grub/grub.conf</p>
<p>更改到如下内容:</p>
<p>kernel /boot/vmlinuz-2.6.18-8.el5 ro root=LABEL=/ elevator=deadline rhgb quiet</p>
<h3 id="系统参数调整"><a href="#系统参数调整" class="headerlink" title="系统参数调整"></a>系统参数调整</h3><h4 id="Linux-系统内核参数优化"><a href="#Linux-系统内核参数优化" class="headerlink" title="Linux 系统内核参数优化"></a>Linux 系统内核参数优化</h4><p>vim/etc/sysctl.conf</p>
<p>net.ipv4.ip_local_port_range = 1024 65535：# 用户端口范围</p>
<p>net.ipv4.tcp_max_syn_backlog = 4096 </p>
<p>net.ipv4.tcp_fin_timeout = 30 </p>
<p>fs.file-max=65535：# 系统最大文件句柄，控制的是能打开文件最大数量  </p>
<h4 id="用户限制参数（MySQL可以不设置以下配置）"><a href="#用户限制参数（MySQL可以不设置以下配置）" class="headerlink" title="用户限制参数（MySQL可以不设置以下配置）"></a>用户限制参数（MySQL可以不设置以下配置）</h4><p>vim/etc/security/limits.conf </p>
<p>* soft nproc 65535</p>
<p>* hard nproc 65535</p>
<p>* soft nofile 65535</p>
<p>* hard nofile 65535</p>
<h3 id="应用优化"><a href="#应用优化" class="headerlink" title="应用优化"></a>应用优化</h3><p>业务应用和数据库应用独立；</p>
<p><strong>防火墙：</strong>iptables、selinux等其他无用服务（关闭）：</p>
<p>   chkconfig —level 23456 acpid off</p>
<p>​    chkconfig —level 23456 anacron off</p>
<p>​    chkconfig —level 23456 autofs off</p>
<p>​    chkconfig —level 23456 avahi-daemon off</p>
<p>​    chkconfig —level 23456 bluetooth off</p>
<p>​    chkconfig —level 23456 cups off</p>
<p>​    chkconfig —level 23456 firstboot off</p>
<p>​    chkconfig —level 23456 haldaemon off</p>
<p>​    chkconfig —level 23456 hplip off</p>
<p>​    chkconfig —level 23456 ip6tables off</p>
<p>​    chkconfig —level 23456 iptables  off</p>
<p>​    chkconfig —level 23456 isdn off</p>
<p>​    chkconfig —level 23456 pcscd off</p>
<p>​    chkconfig —level 23456 sendmail  off</p>
<p>​    chkconfig —level 23456 yum-updatesd  off</p>
<p>安装图形界面的服务器不要启动图形界面runlevel 3。 </p>
<p>另外，思考将来我们的业务是否真的需要MySQL，还是使用其他种类的数据库。用数据库的最高境界就是不用数据库。</p>
<h2 id="数据库优化"><a href="#数据库优化" class="headerlink" title="数据库优化"></a>数据库优化</h2><p>SQL优化方向：执行计划、索引、SQL改写。</p>
<p>架构优化方向：高可用架构、高性能架构、分库分表。</p>
<h3 id="数据库参数优化？"><a href="#数据库参数优化？" class="headerlink" title="数据库参数优化？"></a>数据库参数优化？</h3><h4 id="调整"><a href="#调整" class="headerlink" title="调整"></a>调整</h4><p>实例整体（高级优化，扩展）：</p>
<p>thread_concurrency：# 并发线程数量个数</p>
<p>sort_buffer_size：# 排序缓存</p>
<p>read_buffer_size：# 顺序读取缓存</p>
<p>read_rnd_buffer_size：# 随机读取缓存</p>
<p>key_buffer_size：# 索引缓存</p>
<p>thread_cache_size：# (1G—&gt;8, 2G—&gt;16, 3G—&gt;32, &gt;3G—&gt;64)</p>
<h4 id="连接层（基础优化）"><a href="#连接层（基础优化）" class="headerlink" title="连接层（基础优化）"></a>连接层（基础优化）</h4><p>设置合理的连接客户和连接方式：</p>
<p>max_connections           # 最大连接数，看交易笔数设置    </p>
<p>max_connect_errors        # 最大错误连接数，能大则大</p>
<p>connect_timeout           # 连接超时</p>
<p>max_user_connections      # 最大用户连接数</p>
<p>skip-name-resolve         # 跳过域名解析</p>
<p>wait_timeout              # 等待超时</p>
<p>back_log                  # 可以在堆栈中的连接数量</p>
<h4 id="SQL层（基础优化）"><a href="#SQL层（基础优化）" class="headerlink" title="SQL层（基础优化）"></a>SQL层（基础优化）</h4><p>query_cache_size： 查询缓存  &gt;&gt;&gt;  OLAP类型数据库,需要重点加大此内存缓存，但是一般不会超过GB。</p>
<p>对于经常被修改的数据，缓存会立马失效。</p>
<p>我们可以实用内存数据库（redis、memecache），替代他的功能。</p>
<h3 id="存储层引擎（innodb-基础优化参数）"><a href="#存储层引擎（innodb-基础优化参数）" class="headerlink" title="存储层引擎（innodb 基础优化参数）"></a>存储层引擎（innodb 基础优化参数）</h3><p>default-storage-engine</p>
<p>innodb_buffer_pool_size       # 没有固定大小，50%测试值，看看情况再微调。但是尽量设置不要超过物理内存70%</p>
<p>innodb_file_per_table=(1,0)</p>
<p>innodb_flush_log_at_trx_commit=(0,1,2) # 1是最安全的，0是性能最高，2折中</p>
<p>binlog_sync</p>
<p>Innodb_flush_method=(O_DIRECT, fdatasync)</p>
<p>innodb_log_buffer_size           # 100M以下</p>
<p>innodb_log_file_size               # 100M 以下</p>
<p>innodb_log_files_in_group       # 5个成员以下,一般2-3个够用（iblogfile0-N）</p>
<p>innodb_max_dirty_pages_pct   # 达到百分之75的时候刷写 内存脏页到磁盘。</p>
<p>log_bin</p>
<p>max_binlog_cache_size                     # 可以不设置</p>
<p>max_binlog_size                               # 可以不设置</p>
<p>innodb_additional_mem_pool_size     #小于2G内存的机器，推荐值是20M。32G内存以上100M</p>
<h2 id="谈谈项目中常用的MySQL优化方法，共19条，具体如下："><a href="#谈谈项目中常用的MySQL优化方法，共19条，具体如下：" class="headerlink" title="谈谈项目中常用的MySQL优化方法，共19条，具体如下："></a>谈谈项目中常用的MySQL优化方法，共19条，具体如下：</h2><h3 id="1-Explain"><a href="#1-Explain" class="headerlink" title="1. Explain"></a>1. Explain</h3><p>做MySQL优化，我们要善用EXPLAIN查看SQL执行计划。</p>
<p>下面来个简单的示例，标注（1、2、3、4、5）我们要重点关注的数据：</p>
<p><img src="/xieyuanhui/2019/07/28/SQL优化方案/img3.jpeg" alt></p>
<p>type列，连接类型。一个好的SQL语句至少要达到range级别。杜绝出现all级别。</p>
<p>key列，使用到的索引名。如果没有选择索引，值是NULL。可以采取强制索引方式。</p>
<p>key_len列，索引长度。</p>
<p>rows列，扫描行数。该值是个预估值。</p>
<p>extra列，详细说明。注意，常见的不太友好的值，如下：Using filesort，Using temporary。</p>
<h3 id="2-SQL-语句中-IN-包含的值不应过多"><a href="#2-SQL-语句中-IN-包含的值不应过多" class="headerlink" title="2. SQL 语句中 IN 包含的值不应过多"></a>2. SQL 语句中 IN 包含的值不应过多</h3><p>MySQL对于IN做了相应的优化，即将IN中的常量全部存储在一个数组里面，而且这个数组是排好序的。但是如果数值较多，产生的消耗也是比较大的。再例如：select id from t where num in(1,2,3) 对于连续的数值，能用between就不要用in了；再或者使用连接来替换。</p>
<h3 id="3、SELECT语句务必指明字段名称"><a href="#3、SELECT语句务必指明字段名称" class="headerlink" title="3、SELECT语句务必指明字段名称"></a>3、SELECT语句务必指明字段名称</h3><p>SELECT*增加很多不必要的消耗（CPU、IO、内存、网络带宽）；降低了使用覆盖索引的可能性；当表结构发生改变时，前断也需要更新。所以要求直接在select后面接上字段名。</p>
<h3 id="4、当只需要一条数据的时候，使用limit-1"><a href="#4、当只需要一条数据的时候，使用limit-1" class="headerlink" title="4、当只需要一条数据的时候，使用limit 1"></a>4、当只需要一条数据的时候，使用limit 1</h3><p>这是为了使EXPLAIN中type列达到const类型</p>
<h3 id="5、如果排序字段没有用到索引，就尽量少排序"><a href="#5、如果排序字段没有用到索引，就尽量少排序" class="headerlink" title="5、如果排序字段没有用到索引，就尽量少排序"></a>5、如果排序字段没有用到索引，就尽量少排序</h3><h3 id="6、如果限制条件中其他字段没有索引，尽量少用or"><a href="#6、如果限制条件中其他字段没有索引，尽量少用or" class="headerlink" title="6、如果限制条件中其他字段没有索引，尽量少用or"></a>6、如果限制条件中其他字段没有索引，尽量少用or</h3><p>or两边的字段中，如果有一个不是索引字段，而其他条件也不是索引字段，会造成该查询不走索引的情况。很多时候使用union all或者是union（必要的时候）的方式来代替“or”会得到更好的效果。</p>
<h3 id="7、尽量用union-all代替union"><a href="#7、尽量用union-all代替union" class="headerlink" title="7、尽量用union all代替union"></a>7、尽量用union all代替union</h3><p>union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算，加大资源消耗及延迟。当然，union all的前提条件是两个结果集没有重复数据。</p>
<h3 id="8、不使用ORDER-BY-RAND"><a href="#8、不使用ORDER-BY-RAND" class="headerlink" title="8、不使用ORDER BY RAND()"></a>8、不使用ORDER BY RAND()</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="keyword">id</span> <span class="keyword">from</span> <span class="string">`dynamic`</span> <span class="keyword">order</span> <span class="keyword">by</span> <span class="keyword">rand</span>() <span class="keyword">limit</span> <span class="number">1000</span>;</span><br></pre></td></tr></table></figure>
<p>上面的SQL语句，可优化为：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="keyword">id</span> <span class="keyword">from</span> <span class="string">`dynamic`</span> t1 <span class="keyword">join</span> (<span class="keyword">select</span> <span class="keyword">rand</span>() * (<span class="keyword">select</span> <span class="keyword">max</span>(<span class="keyword">id</span>) <span class="keyword">from</span> <span class="string">`dynamic`</span>) <span class="keyword">as</span> nid) t2 <span class="keyword">on</span> t1.id &gt; t2.nidlimit <span class="number">1000</span>;</span><br></pre></td></tr></table></figure>
<h3 id="9、区分in和exists、not-in和not-exists"><a href="#9、区分in和exists、not-in和not-exists" class="headerlink" title="9、区分in和exists、not in和not exists"></a>9、区分in和exists、not in和not exists</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> 表A <span class="keyword">where</span> <span class="keyword">id</span> <span class="keyword">in</span> (<span class="keyword">select</span> <span class="keyword">id</span> <span class="keyword">from</span> 表B)</span><br></pre></td></tr></table></figure>
<p>上面SQL语句相当于</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> 表A <span class="keyword">where</span> <span class="keyword">exists</span>(<span class="keyword">select</span> * <span class="keyword">from</span> 表B <span class="keyword">where</span> 表B.id=表A.id)</span><br></pre></td></tr></table></figure>
<p>区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询。所以IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。</p>
<p>关于not in和not exists，推荐使用not exists，不仅仅是效率问题，not in可能存在逻辑问题。如何高效的写出一个替代not exists的SQL语句？</p>
<p>原SQL语句：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> colname … <span class="keyword">from</span> A表 <span class="keyword">where</span> a.id <span class="keyword">not</span> <span class="keyword">in</span> (<span class="keyword">select</span> b.id <span class="keyword">from</span> B表)</span><br></pre></td></tr></table></figure>
<p>高效的SQL语句：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> colname … <span class="keyword">from</span> A表 <span class="keyword">Left</span> <span class="keyword">join</span> B表 <span class="keyword">on</span> <span class="keyword">where</span> a.id = b.id <span class="keyword">where</span> b.id <span class="keyword">is</span> <span class="literal">null</span></span><br></pre></td></tr></table></figure>
<p>取出的结果集如下图表示，A表不在B表中的数据：</p>
<p><img src="/xieyuanhui/2019/07/28/SQL优化方案/img4.jpeg" alt></p>
<h3 id="10、使用合理的分页方式以提高分页的效率"><a href="#10、使用合理的分页方式以提高分页的效率" class="headerlink" title="10、使用合理的分页方式以提高分页的效率"></a>10、使用合理的分页方式以提高分页的效率</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="keyword">id</span>,<span class="keyword">name</span> <span class="keyword">from</span> product <span class="keyword">limit</span> <span class="number">866613</span>, <span class="number">20</span></span><br></pre></td></tr></table></figure>
<p>使用上述SQL语句做分页的时候，可能有人会发现，随着表数据量的增加，直接使用limit分页查询会越来越慢。</p>
<p>优化的方法如下：可以取前一页的最大行数的id，然后根据这个最大的id来限制下一页的起点。比如此列中，上一页最大的id是866612。SQL可以采用如下的写法：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="keyword">id</span>,<span class="keyword">name</span> <span class="keyword">from</span> product <span class="keyword">where</span> <span class="keyword">id</span>&gt; <span class="number">866612</span> <span class="keyword">limit</span> <span class="number">20</span></span><br></pre></td></tr></table></figure>
<h3 id="11、分段查询"><a href="#11、分段查询" class="headerlink" title="11、分段查询"></a>11、分段查询</h3><p>一些用户选择页面中，可能一些用户选择的时间范围过大，造成查询缓慢。主要的原因是扫描行数过多。这个时候可以通过程序，分段进行查询，循环遍历，将结果合并处理进行展示。</p>
<p>如下图这个SQL语句，扫描的行数成百万级以上的时候就可以使用分段查询：</p>
<p><img src="/xieyuanhui/2019/07/28/SQL优化方案/img5.jpeg" alt></p>
<h3 id="12、避免在where子句中对字段进行null值判断"><a href="#12、避免在where子句中对字段进行null值判断" class="headerlink" title="12、避免在where子句中对字段进行null值判断"></a>12、避免在where子句中对字段进行null值判断</h3><p>对于null的判断会导致引擎放弃使用索引而进行全表扫描。</p>
<h3 id="13、不建议使用-前缀模糊查询"><a href="#13、不建议使用-前缀模糊查询" class="headerlink" title="13、不建议使用%前缀模糊查询"></a>13、不建议使用%前缀模糊查询</h3><p>例如LIKE“%name”或者LIKE“%name%”，这种查询会导致索引失效而进行全表扫描。但是可以使用LIKE “name%”。</p>
<p>那如何查询%name%？</p>
<p>如下图所示，虽然给secret字段添加了索引，但在explain结果并没有使用：</p>
<p><img src="/xieyuanhui/2019/07/28/SQL优化方案/img6.jpeg" alt></p>
<p>那么如何解决这个问题呢，答案：使用全文索引。</p>
<p>在我们查询中经常会用到select id,fnum,fdst from dynamic_201606 where user_name like ‘%zhangsan%’; 。这样的语句，普通索引是无法满足查询需求的。庆幸的是在MySQL中，有全文索引来帮助我们。</p>
<p>创建全文索引的SQL语法是：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> <span class="string">`dynamic_201606`</span> <span class="keyword">ADD</span> FULLTEXT <span class="keyword">INDEX</span> <span class="string">`idx_user_name`</span> (<span class="string">`user_name`</span>);</span><br></pre></td></tr></table></figure>
<p>使用全文索引的SQL语句是：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="keyword">id</span>,fnum,fdst <span class="keyword">from</span> dynamic_201606 <span class="keyword">where</span> <span class="keyword">match</span>(user_name) against(<span class="string">'zhangsan'</span> <span class="keyword">in</span> <span class="built_in">boolean</span> <span class="keyword">mode</span>);</span><br></pre></td></tr></table></figure>
<p>注意：在需要创建全文索引之前，请联系DBA确定能否创建。同时需要注意的是查询语句的写法与普通索引的区别。</p>
<h3 id="14、避免在where子句中对字段进行表达式操作"><a href="#14、避免在where子句中对字段进行表达式操作" class="headerlink" title="14、避免在where子句中对字段进行表达式操作"></a>14、避免在where子句中对字段进行表达式操作</h3><p>比如：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> user_id,user_project <span class="keyword">from</span> user_base <span class="keyword">where</span> age*<span class="number">2</span>=<span class="number">36</span>;</span><br></pre></td></tr></table></figure>
<p>中对字段就行了算术运算，这会造成引擎放弃使用索引，建议改成：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> user_id,user_project <span class="keyword">from</span> user_base <span class="keyword">where</span> age=<span class="number">36</span>/<span class="number">2</span>;</span><br></pre></td></tr></table></figure>
<h3 id="15、避免隐式类型转换"><a href="#15、避免隐式类型转换" class="headerlink" title="15、避免隐式类型转换"></a>15、避免隐式类型转换</h3><p>where子句中出现column字段的类型和传入的参数类型不一致的时候发生的类型转换，建议先确定where中的参数类型。</p>
<h3 id="16、对于联合索引来说，要遵守最左前缀法则"><a href="#16、对于联合索引来说，要遵守最左前缀法则" class="headerlink" title="16、对于联合索引来说，要遵守最左前缀法则"></a>16、对于联合索引来说，要遵守最左前缀法则</h3><p>举列来说索引含有字段id、name、school，可以直接用id字段，也可以id、name这样的顺序，但是name;school都无法使用这个索引。所以在创建联合索引的时候一定要注意索引字段顺序，常用的查询字段放在最前面。</p>
<h3 id="17、必要时可以使用force-index来强制查询走某个索引"><a href="#17、必要时可以使用force-index来强制查询走某个索引" class="headerlink" title="17、必要时可以使用force index来强制查询走某个索引"></a>17、必要时可以使用force index来强制查询走某个索引</h3><p>有的时候MySQL优化器采取它认为合适的索引来检索SQL语句，但是可能它所采用的索引并不是我们想要的。这时就可以采用forceindex来强制优化器使用我们制定的索引。</p>
<h3 id="18、注意范围查询语句"><a href="#18、注意范围查询语句" class="headerlink" title="18、注意范围查询语句"></a>18、注意范围查询语句</h3><p>对于联合索引来说，如果存在范围查询，比如between、&gt;、&lt;等条件时，会造成后面的索引字段失效。</p>
<h3 id="19、关于JOIN优化"><a href="#19、关于JOIN优化" class="headerlink" title="19、关于JOIN优化"></a>19、关于JOIN优化</h3><p><img src="/xieyuanhui/2019/07/28/SQL优化方案/img7.jpeg" alt></p>
<p>LEFT JOIN A表为驱动表，INNER JOIN MySQL会自动找出那个数据少的表作用驱动表，RIGHT JOIN B表为驱动表。</p>
<p><strong>注意：</strong></p>
<p>1）MySQL中没有full join，可以用以下方式来解决：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> A <span class="keyword">left</span> <span class="keyword">join</span> B <span class="keyword">on</span> B.name = A.namewhere B.name <span class="keyword">is</span> nullunion allselect * <span class="keyword">from</span> B;</span><br></pre></td></tr></table></figure>
<p>2）尽量使用inner join，避免left join：</p>
<p>参与联合查询的表至少为2张表，一般都存在大小之分。如果连接方式是inner join，在没有其他过滤条件的情况下MySQL会自动选择小表作为驱动表，但是left join在驱动表的选择上遵循的是左边驱动右边的原则，即left join左边的表名为驱动表。</p>
<p>3）合理利用索引：</p>
<p>被驱动表的索引字段作为on的限制字段。</p>
<p>4）利用小表去驱动大表：</p>
<p><img src="/xieyuanhui/2019/07/28/SQL优化方案/img8.jpeg" alt></p>
<p>从原理图能够直观的看出如果能够减少驱动表的话，减少嵌套循环中的循环次数，以减少 IO总量及CPU运算的次数。</p>
<p>5）巧用STRAIGHT_JOIN：</p>
<p>inner join是由MySQL选择驱动表，但是有些特殊情况需要选择另个表作为驱动表，比如有group by、order by等「Using filesort」、「Using temporary」时。STRAIGHT_JOIN来强制连接顺序，在STRAIGHT_JOIN左边的表名就是驱动表，右边则是被驱动表。在使用STRAIGHT_JOIN有个前提条件是该查询是内连接，也就是inner join。其他链接不推荐使用STRAIGHT_JOIN，否则可能造成查询结果不准确。</p>
<p><img src="/xieyuanhui/2019/07/28/SQL优化方案/img9.jpeg" alt></p>
<p>这个方式有时能减少3倍的时间。</p>

      
    </div>

    

    
    
    

    
        
<div class="my_post_copyright">
  <script src="//cdn.bootcss.com/clipboard.js/1.5.10/clipboard.min.js"></script>
  
  <!-- JS库 sweetalert 可修改路径 -->
  <script type="text/javascript" src="http://jslibs.wuxubj.cn/sweetalert_mini/jquery-1.7.1.min.js"></script>
  <script src="http://jslibs.wuxubj.cn/sweetalert_mini/sweetalert.min.js"></script>
  <link rel="stylesheet" type="text/css" href="http://jslibs.wuxubj.cn/sweetalert_mini/sweetalert.mini.css">
 
  <p><span>本文标题:</span>SQL优化方案</p>
  <p><span>文章作者:</span>xieyuanhui</p>
  <p><span>发布时间:</span>2019年07月28日 - 14:29:21</p>
  <p><span>最后更新:</span>2019年09月01日 - 10:14:38</p>
  <p><span>原始链接:</span><a href="/xieyuanhui/2019/07/28/SQL优化方案/" title="SQL优化方案">http://xyh5513.gitee.io/xieyuanhui/2019/07/28/SQL优化方案/</a>
    <span class="copy-path" title="点击复制文章链接"><i class="fa fa-clipboard" data-clipboard-text="http://xyh5513.gitee.io/xieyuanhui/2019/07/28/SQL优化方案/" aria-label="复制成功！"></i></span>
  </p>
  <p><span>许可协议:</span><i class="fa fa-creative-commons"></i> <a rel="license" href="https://creativecommons.org/licenses/by-nc-nd/4.0/" target="_blank" title="Attribution-NonCommercial-NoDerivatives 4.0 International (CC BY-NC-ND 4.0)">CC BY-NC-SA 4.0</a> 转载请保留原文链接及作者。</p>  
</div>
<script> 
    var clipboard = new Clipboard('.fa-clipboard');
    clipboard.on('success', $(function(){
      $(".fa-clipboard").click(function(){
        swal({   
          title: "",   
          text: '复制成功',   
          html: false,
          timer: 500,   
          showConfirmButton: false
        });
      });
    }));  
</script>

    

    

    
      
    
    
      <div>
        <div id="reward-container">
  <div>您的支持将鼓励我继续创作</div>
  <button id="reward-button" disable="enable" onclick="var qr = document.getElementById(&quot;qr&quot;); qr.style.display = (qr.style.display === 'none') ? 'block' : 'none';">
    打赏
  </button>
  <div id="qr" style="display: none;">

    
      
      
        
      
      <div style="display: inline-block">
        <img src="/xieyuanhui/images/wechatpay.png" alt="xieyuanhui 微信支付">
        <p>微信支付</p>
      </div>
    
      
      
        
      
      <div style="display: inline-block">
        <img src="/xieyuanhui/images/alipay.jpg" alt="xieyuanhui 支付宝">
        <p>支付宝</p>
      </div>
    

  </div>
</div>

      </div>
    

    

    <footer class="post-footer">
      
        <div class="post-tags">
          
            <a href="/xieyuanhui/tags/数据库/" rel="tag"># 数据库</a>
          
            <a href="/xieyuanhui/tags/SQL/" rel="tag"># SQL</a>
          
        </div>
      

      
      
        <div class="post-widgets">
        

        

        
          
          <div class="social_share">
            
            
              <div id="needsharebutton-postbottom">
                <span class="btn">
                  <i class="fa fa-share-alt" aria-hidden="true"></i>
                </span>
              </div>
            
            
          </div>
        
        </div>
      
      

      
        <div class="post-nav">
          <div class="post-nav-next post-nav-item">
            
              <a href="/xieyuanhui/2019/07/28/Elasticsearch简介/" rel="next" title="Elasticsearch简介">
                <i class="fa fa-chevron-left"></i> Elasticsearch简介
              </a>
            
          </div>

          <span class="post-nav-divider"></span>

          <div class="post-nav-prev post-nav-item">
            
              <a href="/xieyuanhui/2019/08/04/进程间的五种通信方式介绍/" rel="prev" title="进程间的五种通信方式介绍">
                进程间的五种通信方式介绍 <i class="fa fa-chevron-right"></i>
              </a>
            
          </div>
        </div>
      

      
      
    </footer>
  </div>
  
  
  
  </article>


  </div>


          </div>
          

  



        </div>
        
          
  
  <div class="sidebar-toggle">
    <div class="sidebar-toggle-line-wrap">
      <span class="sidebar-toggle-line sidebar-toggle-line-first"></span>
      <span class="sidebar-toggle-line sidebar-toggle-line-middle"></span>
      <span class="sidebar-toggle-line sidebar-toggle-line-last"></span>
    </div>
  </div>

  <aside id="sidebar" class="sidebar">
    <div class="sidebar-inner">

      

      
        <ul class="sidebar-nav motion-element">
          <li class="sidebar-nav-toc sidebar-nav-active" data-target="post-toc-wrap">
            文章目录
          </li>
          <li class="sidebar-nav-overview" data-target="site-overview-wrap">
            站点概览
          </li>
        </ul>
      

      <div class="site-overview-wrap sidebar-panel">
        <div class="site-overview">
          <div class="site-author motion-element" itemprop="author" itemscope itemtype="http://schema.org/Person">
            
              <img class="site-author-image" itemprop="image" src="/xieyuanhui/images/deer.png" alt="xieyuanhui">
            
              <p class="site-author-name" itemprop="name">xieyuanhui</p>
              <div class="site-description motion-element" itemprop="description"></div>
          </div>

          
            <nav class="site-state motion-element">
              
                <div class="site-state-item site-state-posts">
                
                  <a href="/xieyuanhui/archives/">
                
                    <span class="site-state-item-count">107</span>
                    <span class="site-state-item-name">日志</span>
                  </a>
                </div>
              

              
                
                
                <div class="site-state-item site-state-categories">
                  
                    
                      <a href="/xieyuanhui/categories/">
                    
                  
                    
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                    <span class="site-state-item-count">16</span>
                    <span class="site-state-item-name">分类</span>
                  </a>
                </div>
              

              
                
                
                <div class="site-state-item site-state-tags">
                  
                    
                      <a href="/xieyuanhui/tags/">
                    
                  
                    
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                      
                    
                    <span class="site-state-item-count">114</span>
                    <span class="site-state-item-name">标签</span>
                  </a>
                </div>
              
            </nav>
          

          
            <div class="feed-link motion-element">
              <a href="/xieyuanhui/atom.xml" rel="alternate">
                <i class="fa fa-rss"></i>
                RSS
              </a>
            </div>
          

          

          
            <div class="links-of-author motion-element">
              
                <span class="links-of-author-item">
                  
                  
                    
                  
                  
                    
                  
                  <a href="/xieyuanhui/yuanhxie@163.com" title="E-Mail &rarr; yuanhxie@163.com"><i class="fa fa-fw fa-envelope"></i>E-Mail</a>
                </span>
              
            </div>
          

          

          
          

          
            
          
          

        </div>
      </div>

      
      <!--noindex-->
        <div class="post-toc-wrap motion-element sidebar-panel sidebar-panel-active">
          <div class="post-toc">

            
            
            
            

            
              <div class="post-toc-content"><ol class="nav"><li class="nav-item nav-level-2"><a class="nav-link" href="#优化的哲学"><span class="nav-text">优化的哲学</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#优化可能带来的问题？"><span class="nav-text">优化可能带来的问题？</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#优化的需求？"><span class="nav-text">优化的需求？</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#优化由谁参与？"><span class="nav-text">优化由谁参与？</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#优化思路"><span class="nav-text">优化思路</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#优化什么？"><span class="nav-text">优化什么？</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#优化的范围有哪些？"><span class="nav-text">优化的范围有哪些？</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#存储、主机和操作系统方面："><span class="nav-text">存储、主机和操作系统方面：</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#应用程序方面："><span class="nav-text">应用程序方面：</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#数据库优化方面："><span class="nav-text">数据库优化方面：</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#优化维度？"><span class="nav-text">优化维度？</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#数据库的优化维度有四个："><span class="nav-text">数据库的优化维度有四个：</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#优化选择："><span class="nav-text">优化选择：</span></a></li></ol></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#优化工具有啥？"><span class="nav-text">优化工具有啥？</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#数据库层面"><span class="nav-text">数据库层面</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#数据库层面问题解决思路？"><span class="nav-text">数据库层面问题解决思路？</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#系统层面"><span class="nav-text">系统层面</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#CPU-方面"><span class="nav-text">CPU 方面</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#内存"><span class="nav-text">内存</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#IO-设备（磁盘、网络）"><span class="nav-text">IO 设备（磁盘、网络）</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#vmstat-命令说明："><span class="nav-text">vmstat 命令说明：</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#iostat-命令说明："><span class="nav-text">iostat 命令说明：</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#系统层面问题解决办法？"><span class="nav-text">系统层面问题解决办法？</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#CPU-负载高，IO-负载低："><span class="nav-text">CPU 负载高，IO 负载低：</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#IO-负载高，CPU-负载低"><span class="nav-text">IO 负载高，CPU 负载低</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#IO-和-CPU-负载都很高"><span class="nav-text">IO 和 CPU 负载都很高</span></a></li></ol></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#基础优化"><span class="nav-text">基础优化</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#优化思路？"><span class="nav-text">优化思路？</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#硬件优化？"><span class="nav-text">硬件优化？</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#主机方面："><span class="nav-text">主机方面：</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#CPU-的选择"><span class="nav-text">CPU 的选择</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#内存的选择"><span class="nav-text">内存的选择</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#存储方面"><span class="nav-text">存储方面</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#网络设备方面"><span class="nav-text">网络设备方面</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#服务器硬件优化"><span class="nav-text">服务器硬件优化</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#系统优化"><span class="nav-text">系统优化</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#CPU："><span class="nav-text">CPU：</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#内存："><span class="nav-text">内存：</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#SWAP："><span class="nav-text">SWAP：</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#IO"><span class="nav-text">IO</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#SWAP-调整（不使用-swap-分区）"><span class="nav-text">SWAP 调整（不使用 swap 分区）</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#修改MySQL的配置参数-innodb-flush-method，开启O-DIRECT模式："><span class="nav-text">修改MySQL的配置参数**innodb_flush_ method，开启O_DIRECT模式：</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#IO-调度策略："><span class="nav-text">IO 调度策略：</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#系统参数调整"><span class="nav-text">系统参数调整</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#Linux-系统内核参数优化"><span class="nav-text">Linux 系统内核参数优化</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#用户限制参数（MySQL可以不设置以下配置）"><span class="nav-text">用户限制参数（MySQL可以不设置以下配置）</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#应用优化"><span class="nav-text">应用优化</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#数据库优化"><span class="nav-text">数据库优化</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#数据库参数优化？"><span class="nav-text">数据库参数优化？</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#调整"><span class="nav-text">调整</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#连接层（基础优化）"><span class="nav-text">连接层（基础优化）</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#SQL层（基础优化）"><span class="nav-text">SQL层（基础优化）</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#存储层引擎（innodb-基础优化参数）"><span class="nav-text">存储层引擎（innodb 基础优化参数）</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#谈谈项目中常用的MySQL优化方法，共19条，具体如下："><span class="nav-text">谈谈项目中常用的MySQL优化方法，共19条，具体如下：</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#1-Explain"><span class="nav-text">1. Explain</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#2-SQL-语句中-IN-包含的值不应过多"><span class="nav-text">2. SQL 语句中 IN 包含的值不应过多</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#3、SELECT语句务必指明字段名称"><span class="nav-text">3、SELECT语句务必指明字段名称</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#4、当只需要一条数据的时候，使用limit-1"><span class="nav-text">4、当只需要一条数据的时候，使用limit 1</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#5、如果排序字段没有用到索引，就尽量少排序"><span class="nav-text">5、如果排序字段没有用到索引，就尽量少排序</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#6、如果限制条件中其他字段没有索引，尽量少用or"><span class="nav-text">6、如果限制条件中其他字段没有索引，尽量少用or</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#7、尽量用union-all代替union"><span class="nav-text">7、尽量用union all代替union</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#8、不使用ORDER-BY-RAND"><span class="nav-text">8、不使用ORDER BY RAND()</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#9、区分in和exists、not-in和not-exists"><span class="nav-text">9、区分in和exists、not in和not exists</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#10、使用合理的分页方式以提高分页的效率"><span class="nav-text">10、使用合理的分页方式以提高分页的效率</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#11、分段查询"><span class="nav-text">11、分段查询</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#12、避免在where子句中对字段进行null值判断"><span class="nav-text">12、避免在where子句中对字段进行null值判断</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#13、不建议使用-前缀模糊查询"><span class="nav-text">13、不建议使用%前缀模糊查询</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#14、避免在where子句中对字段进行表达式操作"><span class="nav-text">14、避免在where子句中对字段进行表达式操作</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#15、避免隐式类型转换"><span class="nav-text">15、避免隐式类型转换</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#16、对于联合索引来说，要遵守最左前缀法则"><span class="nav-text">16、对于联合索引来说，要遵守最左前缀法则</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#17、必要时可以使用force-index来强制查询走某个索引"><span class="nav-text">17、必要时可以使用force index来强制查询走某个索引</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#18、注意范围查询语句"><span class="nav-text">18、注意范围查询语句</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#19、关于JOIN优化"><span class="nav-text">19、关于JOIN优化</span></a></li></ol></li></ol></div>
            

          </div>
        </div>
      <!--/noindex-->
      

      

    </div>
  </aside>
  


        
      </div>
    </main>

    <footer id="footer" class="footer">
      <div class="footer-inner">
        <div class="copyright">&copy; <span itemprop="copyrightYear">2019</span>
  <span class="with-love" id="animate">
    <i class="fa fa-user"></i>
  </span>
  <span class="author" itemprop="copyrightHolder">xieyuanhui</span>

  
    <span class="post-meta-divider">|</span>
    <span class="post-meta-item-icon">
      <i class="fa fa-area-chart"></i>
    </span>
    
      <span class="post-meta-item-text">站点总字数：</span>
    
    <span title="站点总字数">749k</span>
  

  
    <span class="post-meta-divider">|</span>
    <span class="post-meta-item-icon">
      <i class="fa fa-coffee"></i>
    </span>
    
      <span class="post-meta-item-text">站点阅读时长 &asymp;</span>
    
    <span title="站点阅读时长">11:21</span>
  
</div>


  <div class="powered-by">由 <a href="https://hexo.io" class="theme-link" rel="noopener" target="_blank">Hexo</a> 强力驱动 v3.8.0</div>



  <span class="post-meta-divider">|</span>



  <div class="theme-info">主题 – <a href="https://theme-next.org" class="theme-link" rel="noopener" target="_blank">NexT.Pisces</a> v7.1.0</div>


<div><span id="sitetime"></span>
<script language="javascript">
  function siteTime(){
    window.setTimeout("siteTime()", 1000);
    var seconds = 1000;
    var minutes = seconds * 60;
    var hours = minutes * 60;
    var days = hours * 24;
    var years = days * 365;
    var today = new Date();
    var todayYear = today.getFullYear();
    var todayMonth = today.getMonth()+1;
    var todayDate = today.getDate();
    var todayHour = today.getHours();
    var todayMinute = today.getMinutes();
    var todaySecond = today.getSeconds();
    /* Date.UTC() -- 返回date对象距世界标准时间(UTC)1970年1月1日午夜之间的毫秒数(时间戳)
    year - 作为date对象的年份，为4位年份值
    month - 0-11之间的整数，做为date对象的月份
    day - 1-31之间的整数，做为date对象的天数
    hours - 0(午夜24点)-23之间的整数，做为date对象的小时数
    minutes - 0-59之间的整数，做为date对象的分钟数
    seconds - 0-59之间的整数，做为date对象的秒数
    microseconds - 0-999之间的整数，做为date对象的毫秒数 */
    var t1 = Date.UTC(2019,04,09,15,00,00); //北京时间2018-2-13 00:00:00
    var t2 = Date.UTC(todayYear,todayMonth,todayDate,todayHour,todayMinute,todaySecond);
    var diff = t2-t1;
    var diffYears = Math.floor(diff/years);
    var diffDays = Math.floor((diff/days)-diffYears*365);
    var diffHours = Math.floor((diff-(diffYears*365+diffDays)*days)/hours);
    var diffMinutes = Math.floor((diff-(diffYears*365+diffDays)*days-diffHours*hours)/minutes);
    var diffSeconds = Math.floor((diff-(diffYears*365+diffDays)*days-diffHours*hours-diffMinutes*minutes)/seconds);
    document.getElementById("sitetime").innerHTML=" xieyuanhui的个人笔记已运行"+/*diffYears+" 年 "+*/diffDays+" 天 "+diffHours+" 小时 "+diffMinutes+" 分钟 "+diffSeconds+" 秒";
  }/*因为建站时间还没有一年，就将之注释掉了。需要的可以取消*/
  siteTime();
</script></div>



<div>

  <div class="theme-info">
    <script async src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>
    <span id="busuanzi_container_site_pv">本站总访问量<span id="busuanzi_value_site_pv"></span>次</span>
    <span class="post-meta-divider">|</span>
    <span id="busuanzi_container_site_uv">本站访客数<span id="busuanzi_value_site_uv"></span>人</span>
  </div>

</div>

        






  <div>
    <script type="text/javascript">
	    var cnzz_protocol = (("https:" == document.location.protocol) ? "https://" : "http://");
	    document.write(unescape("%3Cspan id='cnzz_stat_icon_1277235632'%3E%3C/span%3E%3Cscript 
	    src='" + cnzz_protocol + "s23.cnzz.com/z_stat.php%3Fid%3D1277235632%26show%3Dpic' 
	    type='text/javascript'%3E%3C/script%3E"));
    </script>
  </div>



        
      </div>
    </footer>

    
      <div class="back-to-top">
        <i class="fa fa-arrow-up"></i>
        
      </div>
    

    
      <div id="needsharebutton-float">
        <span class="btn">
          <i class="fa fa-share-alt" aria-hidden="true"></i>
        </span>
      </div>
    

    

    
  </div>

  

<script>
  if (Object.prototype.toString.call(window.Promise) !== '[object Function]') {
    window.Promise = null;
  }
</script>












  



  
    
    
  
  <script color="0,0,255" opacity="0.5" zindex="-1" count="99" src="/xieyuanhui/lib/canvas-nest/canvas-nest.min.js"></script>













  
  <script src="/xieyuanhui/lib/jquery/index.js?v=2.1.3"></script>

  
  <script src="/xieyuanhui/lib/velocity/velocity.min.js?v=1.2.1"></script>

  
  <script src="/xieyuanhui/lib/velocity/velocity.ui.min.js?v=1.2.1"></script>

  
  <script src="/xieyuanhui/lib/fancybox/source/jquery.fancybox.pack.js"></script>


  


  <script src="/xieyuanhui/js/utils.js?v=7.1.0"></script>

  <script src="/xieyuanhui/js/motion.js?v=7.1.0"></script>



  
  


  <script src="/xieyuanhui/js/affix.js?v=7.1.0"></script>

  <script src="/xieyuanhui/js/schemes/pisces.js?v=7.1.0"></script>



  
  <script src="/xieyuanhui/js/scrollspy.js?v=7.1.0"></script>
<script src="/xieyuanhui/js/post-details.js?v=7.1.0"></script>



  


  <script src="/xieyuanhui/js/next-boot.js?v=7.1.0"></script>


  

  

  

  


  


  
  <script>
    // Popup Window;
    var isfetched = false;
    var isXml = true;
    // Search DB path;
    var search_path = "search.xml";
    if (search_path.length === 0) {
      search_path = "search.xml";
    } else if (/json$/i.test(search_path)) {
      isXml = false;
    }
    var path = "/xieyuanhui/" + search_path;
    // monitor main search box;

    var onPopupClose = function (e) {
      $('.popup').hide();
      $('#local-search-input').val('');
      $('.search-result-list').remove();
      $('#no-result').remove();
      $(".local-search-pop-overlay").remove();
      $('body').css('overflow', '');
    }

    function proceedsearch() {
      $("body")
        .append('<div class="search-popup-overlay local-search-pop-overlay"></div>')
        .css('overflow', 'hidden');
      $('.search-popup-overlay').click(onPopupClose);
      $('.popup').toggle();
      var $localSearchInput = $('#local-search-input');
      $localSearchInput.attr("autocapitalize", "none");
      $localSearchInput.attr("autocorrect", "off");
      $localSearchInput.focus();
    }

    // search function;
    var searchFunc = function(path, search_id, content_id) {
      'use strict';

      // start loading animation
      $("body")
        .append('<div class="search-popup-overlay local-search-pop-overlay">' +
          '<div id="search-loading-icon">' +
          '<i class="fa fa-spinner fa-pulse fa-5x fa-fw"></i>' +
          '</div>' +
          '</div>')
        .css('overflow', 'hidden');
      $("#search-loading-icon").css('margin', '20% auto 0 auto').css('text-align', 'center');

      

      $.ajax({
        url: path,
        dataType: isXml ? "xml" : "json",
        async: true,
        success: function(res) {
          // get the contents from search data
          isfetched = true;
          $('.popup').detach().appendTo('.header-inner');
          var datas = isXml ? $("entry", res).map(function() {
            return {
              title: $("title", this).text(),
              content: $("content",this).text(),
              url: $("url" , this).text()
            };
          }).get() : res;
          var input = document.getElementById(search_id);
          var resultContent = document.getElementById(content_id);
          var inputEventFunction = function() {
            var searchText = input.value.trim().toLowerCase();
            var keywords = searchText.split(/[\s\-]+/);
            if (keywords.length > 1) {
              keywords.push(searchText);
            }
            var resultItems = [];
            if (searchText.length > 0) {
              // perform local searching
              datas.forEach(function(data) {
                var isMatch = false;
                var hitCount = 0;
                var searchTextCount = 0;
                var title = data.title.trim();
                var titleInLowerCase = title.toLowerCase();
                var content = data.content.trim().replace(/<[^>]+>/g,"");
                
                var contentInLowerCase = content.toLowerCase();
                var articleUrl = decodeURIComponent(data.url).replace(/\/{2,}/g, '/');
                var indexOfTitle = [];
                var indexOfContent = [];
                // only match articles with not empty titles
                if(title != '') {
                  keywords.forEach(function(keyword) {
                    function getIndexByWord(word, text, caseSensitive) {
                      var wordLen = word.length;
                      if (wordLen === 0) {
                        return [];
                      }
                      var startPosition = 0, position = [], index = [];
                      if (!caseSensitive) {
                        text = text.toLowerCase();
                        word = word.toLowerCase();
                      }
                      while ((position = text.indexOf(word, startPosition)) > -1) {
                        index.push({position: position, word: word});
                        startPosition = position + wordLen;
                      }
                      return index;
                    }

                    indexOfTitle = indexOfTitle.concat(getIndexByWord(keyword, titleInLowerCase, false));
                    indexOfContent = indexOfContent.concat(getIndexByWord(keyword, contentInLowerCase, false));
                  });
                  if (indexOfTitle.length > 0 || indexOfContent.length > 0) {
                    isMatch = true;
                    hitCount = indexOfTitle.length + indexOfContent.length;
                  }
                }

                // show search results

                if (isMatch) {
                  // sort index by position of keyword

                  [indexOfTitle, indexOfContent].forEach(function (index) {
                    index.sort(function (itemLeft, itemRight) {
                      if (itemRight.position !== itemLeft.position) {
                        return itemRight.position - itemLeft.position;
                      } else {
                        return itemLeft.word.length - itemRight.word.length;
                      }
                    });
                  });

                  // merge hits into slices

                  function mergeIntoSlice(text, start, end, index) {
                    var item = index[index.length - 1];
                    var position = item.position;
                    var word = item.word;
                    var hits = [];
                    var searchTextCountInSlice = 0;
                    while (position + word.length <= end && index.length != 0) {
                      if (word === searchText) {
                        searchTextCountInSlice++;
                      }
                      hits.push({position: position, length: word.length});
                      var wordEnd = position + word.length;

                      // move to next position of hit

                      index.pop();
                      while (index.length != 0) {
                        item = index[index.length - 1];
                        position = item.position;
                        word = item.word;
                        if (wordEnd > position) {
                          index.pop();
                        } else {
                          break;
                        }
                      }
                    }
                    searchTextCount += searchTextCountInSlice;
                    return {
                      hits: hits,
                      start: start,
                      end: end,
                      searchTextCount: searchTextCountInSlice
                    };
                  }

                  var slicesOfTitle = [];
                  if (indexOfTitle.length != 0) {
                    slicesOfTitle.push(mergeIntoSlice(title, 0, title.length, indexOfTitle));
                  }

                  var slicesOfContent = [];
                  while (indexOfContent.length != 0) {
                    var item = indexOfContent[indexOfContent.length - 1];
                    var position = item.position;
                    var word = item.word;
                    // cut out 100 characters
                    var start = position - 20;
                    var end = position + 80;
                    if(start < 0){
                      start = 0;
                    }
                    if (end < position + word.length) {
                      end = position + word.length;
                    }
                    if(end > content.length){
                      end = content.length;
                    }
                    slicesOfContent.push(mergeIntoSlice(content, start, end, indexOfContent));
                  }

                  // sort slices in content by search text's count and hits' count

                  slicesOfContent.sort(function (sliceLeft, sliceRight) {
                    if (sliceLeft.searchTextCount !== sliceRight.searchTextCount) {
                      return sliceRight.searchTextCount - sliceLeft.searchTextCount;
                    } else if (sliceLeft.hits.length !== sliceRight.hits.length) {
                      return sliceRight.hits.length - sliceLeft.hits.length;
                    } else {
                      return sliceLeft.start - sliceRight.start;
                    }
                  });

                  // select top N slices in content

                  var upperBound = parseInt('1');
                  if (upperBound >= 0) {
                    slicesOfContent = slicesOfContent.slice(0, upperBound);
                  }

                  // highlight title and content

                  function highlightKeyword(text, slice) {
                    var result = '';
                    var prevEnd = slice.start;
                    slice.hits.forEach(function (hit) {
                      result += text.substring(prevEnd, hit.position);
                      var end = hit.position + hit.length;
                      result += '<b class="search-keyword">' + text.substring(hit.position, end) + '</b>';
                      prevEnd = end;
                    });
                    result += text.substring(prevEnd, slice.end);
                    return result;
                  }

                  var resultItem = '';

                  if (slicesOfTitle.length != 0) {
                    resultItem += "<li><a href='" + articleUrl + "' class='search-result-title'>" + highlightKeyword(title, slicesOfTitle[0]) + "</a>";
                  } else {
                    resultItem += "<li><a href='" + articleUrl + "' class='search-result-title'>" + title + "</a>";
                  }

                  slicesOfContent.forEach(function (slice) {
                    resultItem += "<a href='" + articleUrl + "'>" +
                      "<p class=\"search-result\">" + highlightKeyword(content, slice) +
                      "...</p>" + "</a>";
                  });

                  resultItem += "</li>";
                  resultItems.push({
                    item: resultItem,
                    searchTextCount: searchTextCount,
                    hitCount: hitCount,
                    id: resultItems.length
                  });
                }
              })
            };
            if (keywords.length === 1 && keywords[0] === "") {
              resultContent.innerHTML = '<div id="no-result"><i class="fa fa-search fa-5x"></i></div>'
            } else if (resultItems.length === 0) {
              resultContent.innerHTML = '<div id="no-result"><i class="fa fa-frown-o fa-5x"></i></div>'
            } else {
              resultItems.sort(function (resultLeft, resultRight) {
                if (resultLeft.searchTextCount !== resultRight.searchTextCount) {
                  return resultRight.searchTextCount - resultLeft.searchTextCount;
                } else if (resultLeft.hitCount !== resultRight.hitCount) {
                  return resultRight.hitCount - resultLeft.hitCount;
                } else {
                  return resultRight.id - resultLeft.id;
                }
              });
              var searchResultList = '<ul class=\"search-result-list\">';
              resultItems.forEach(function (result) {
                searchResultList += result.item;
              })
              searchResultList += "</ul>";
              resultContent.innerHTML = searchResultList;
            }
          }

          if ('auto' === 'auto') {
            input.addEventListener('input', inputEventFunction);
          } else {
            $('.search-icon').click(inputEventFunction);
            input.addEventListener('keypress', function (event) {
              if (event.keyCode === 13) {
                inputEventFunction();
              }
            });
          }

          // remove loading animation
          $(".local-search-pop-overlay").remove();
          $('body').css('overflow', '');

          proceedsearch();
        }
      });
    }

    // handle and trigger popup window;
    $('.popup-trigger').click(function(e) {
      e.stopPropagation();
      if (isfetched === false) {
        searchFunc(path, 'local-search-input', 'local-search-result');
      } else {
        proceedsearch();
      };
    });

    $('.popup-btn-close').click(onPopupClose);
    $('.popup').click(function(e){
      e.stopPropagation();
    });
    $(document).on('keyup', function (event) {
      var shouldDismissSearchPopup = event.which === 27 &&
        $('.search-popup').is(':visible');
      if (shouldDismissSearchPopup) {
        onPopupClose();
      }
    });
  </script>





  
  
  <script>
    
    function addCount(Counter) {
      var $visitors = $('.leancloud_visitors');
      var url = $visitors.attr('id').trim();
      var title = $visitors.attr('data-flag-title').trim();

      Counter('get', '/classes/Counter', { where: JSON.stringify({ url }) })
        .done(function({ results }) {
          if (results.length > 0) {
            var counter = results[0];
            
            Counter('put', '/classes/Counter/' + counter.objectId, JSON.stringify({ time: { '__op': 'Increment', 'amount': 1 } }))
            
              .done(function() {
                var $element = $(document.getElementById(url));
                $element.find('.leancloud-visitors-count').text(counter.time + 1);
              })
            
              .fail(function ({ responseJSON }) {
                console.log('Failed to save Visitor num, with error message: ' + responseJSON.error);
              })
          } else {
            
              Counter('post', '/classes/Counter', JSON.stringify({ title: title, url: url, time: 1 }))
                .done(function() {
                  var $element = $(document.getElementById(url));
                  $element.find('.leancloud-visitors-count').text(1);
                })
                .fail(function() {
                  console.log('Failed to create');
                });
            
          }
        })
        .fail(function ({ responseJSON }) {
          console.log('LeanCloud Counter Error: ' + responseJSON.code + ' ' + responseJSON.error);
        });
    }
    

    $(function() {
      $.get('https://app-router.leancloud.cn/2/route?appId=' + 'q2BU0OM2W8i5nARddHRKQOvm-gzGzoHsz')
        .done(function({ api_server }) {
          var Counter = function(method, url, data) {
            return $.ajax({
              method: method,
              url: 'https://' + api_server + '/1.1' + url,
              headers: {
                'X-LC-Id': 'q2BU0OM2W8i5nARddHRKQOvm-gzGzoHsz',
                'X-LC-Key': 'hLTPk12Jmt8atnC9cePjTwQH',
                'Content-Type': 'application/json',
              },
              data: data
            });
          };
          
            addCount(Counter);
          
        });
    });
  </script>



  

  
  

  
  

  
    
      <script type="text/x-mathjax-config">
  

  MathJax.Hub.Config({
    tex2jax: {
      inlineMath: [ ['$', '$'], ['\\(', '\\)'] ],
      processEscapes: true,
      skipTags: ['script', 'noscript', 'style', 'textarea', 'pre', 'code']
    },
    TeX: {
      
      equationNumbers: {
        autoNumber: 'AMS'
      }
    }
  });
  MathJax.Hub.Register.StartupHook('TeX Jax Ready', function() {
    MathJax.InputJax.TeX.prefilterHooks.Add(function(data) {
      if (data.display) {
        var next = data.script.nextSibling;
        while (next && next.nodeName.toLowerCase() === '#text') { next = next.nextSibling }
        if (next && next.nodeName.toLowerCase() === 'br') { next.parentNode.removeChild(next) }
      }
    });
  });
</script>

<script type="text/x-mathjax-config">
  MathJax.Hub.Queue(function() {
    var all = MathJax.Hub.getAllJax(), i;
    for (i = 0; i < all.length; i += 1) {
      document.getElementById(all[i].inputID + '-Frame').parentNode.className += ' has-jax';
    }
  });
</script>
<script src="//cdnjs.cloudflare.com/ajax/libs/mathjax/2.7.5/MathJax.js?config=TeX-MML-AM_CHTML"></script>

    
  


  

  

  

  

  
  
  
  <script src="/xieyuanhui/lib/needsharebutton/needsharebutton.js"></script>
  <script>
    
      pbOptions = {};
      
        pbOptions.iconStyle = "box";
      
        pbOptions.boxForm = "horizontal";
      
        pbOptions.position = "bottomCenter";
      
        pbOptions.networks = "Weibo,Wechat,Douban,QQZone,Twitter,Facebook";
      
      new needShareButton('#needsharebutton-postbottom', pbOptions);
    
    
      flOptions = {};
      
        flOptions.iconStyle = "box";
      
        flOptions.boxForm = "horizontal";
      
        flOptions.position = "middleRight";
      
        flOptions.networks = "Weibo,Wechat,Douban,QQZone,Twitter,Facebook";
      
      new needShareButton('#needsharebutton-float', flOptions);
    
  </script>


  

  

  

  

  

  


  
  <script type="text/javascript" src="//cdn.bootcss.com/canvas-nest.js/1.0.0/canvas-nest.min.js"></script><!-- hexo-inject:begin --><!-- Begin: Injected MathJax -->
<script type="text/x-mathjax-config">
  MathJax.Hub.Config({"tex2jax":{"inlineMath":[["$","$"],["\\(","\\)"]],"skipTags":["script","noscript","style","textarea","pre","code"],"processEscapes":true},"TeX":{"equationNumbers":{"autoNumber":"AMS"}}});
</script>

<script type="text/x-mathjax-config">
  MathJax.Hub.Queue(function() {
    var all = MathJax.Hub.getAllJax(), i;
    for(i=0; i < all.length; i += 1) {
      all[i].SourceElement().parentNode.className += ' has-jax';
    }
  });
</script>

<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/mathjax/2.7.1/MathJax.js">
</script>
<!-- End: Injected MathJax -->
<!-- hexo-inject:end -->
  
</body>
</html>
